﻿<cfsilent>
	<cfsetting requesttimeout="300" />
	<cfflush interval="1024" />
	<cfscript>
		
		temFile = GetTempDirectory() & createUUID() & ".xls";

		studentChange = 0;
		studentError = 0;
		studentSkip = 0;
		
		targetArgs = structNew();
		structInsert(targetArgs, "TabID", "labStudentDebt", true);
		
		studentAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		sequenceStudentChange = getProperty("serviceFactory").getBean("sequenceStudentChange");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		
		tchId = sessionAdvice.getAutherUserID();
		termId = event.getArg("TermID");
		changeType = event.getArg("ChangeTypeCode");
		
	</cfscript>
</cfsilent>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>导入转专业数据</title>
<link href="<cfoutput>#getProperty('cdnServer')#</cfoutput><cfoutput>#getContextRoot()#</cfoutput>/acadmicRes/assets/css/impoter.css" rel="stylesheet" type="text/css">
</head>

<body>

	<cfif isDefined("FORM.upload")>
		
		<!--- 将文件保存至临时目录 --->
		<cftry>
			<cffile action="upload" fileField="upload" destination="#temFile#" nameconflict="overwrite">
			<cfcatch type="application">
				<p class="error">上传的文件类型不正确, 请使用电子表格作为数据导入文件.</p>
			</cfcatch>
		</cftry>
		
		<!--- 将文件转换为电子表格对象 --->
		<cfif fileExists(temFile)>
			<cftry>
				<cfspreadsheet action="read" src="#temFile#" excludeHeaderRow="true" headerrow="1" query="rs" sheetname="转专业数据">
				<cfcatch type="application">
					<p class="error">电子表格文件格式不正确, 请使用下载的模板文件录入学籍信息</p>
				</cfcatch>
			</cftry>
			
			<!--- 文件已成功转换为查询对象 --->
			<cfif isDefined("rs") and isQuery(rs)>

				<p class="success">尝试读取电子表格数据 ... 完成</p>
				
				<!--- 检查必要数据字段 --->
				<cfset columnChecked = true />
				<cfif not listFindNoCase(rs.columnList, "学号")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "姓名")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "班级")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入班级")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入专业")><cfset columnChecked = false /></cfif>
				<cfif not listFindNoCase(rs.columnList, "转入学院")><cfset columnChecked = false /></cfif>

				<cfif not columnChecked>
					<p class="error">电子表格数据列不完整, 请检查模板文件是否包含以下必须字段信息: 学号,姓名,班级,转入班级,转入专业,转入学院</p>
				</cfif>

				<cfif columnChecked>

					<p class="success">正在检查数据子项 ... 完成</p> 
				
					<cftransaction>
					
						<cfloop query="rs">
						
							<!---收集Excel行数据--->	
							<cfset studentId = rs["学号"][rs.currentRow] />
							<cfset studentName = rs["姓名"][rs.currentRow] />
							<cfset className = rs["班级"][rs.currentRow] />

							<cfset targetClassName = rs["转入班级"][rs.currentRow] />
							<cfset targetSubjectName = rs["转入专业"][rs.currentRow] />
							<cfset targetInstituteName = rs["转入学院"][rs.currentRow] />

							<cfset approveDocument = listFindNoCase(rs.columnList, "审批文号") AND len(rs["审批文号"][rs.currentRow]) ? rs["审批文号"][rs.currentRow] : "" />

							<!---系统变量---->
							<cfset sql = "	SELECT
												s.sch_name
											FROM
												t_school s" />
							
							<cfset queryObj = new Query( datasource=application.dnsMaster )/>
							<cfset rs_school = queryObj.execute( sql=sql ).getResult()/>
							
							<cfset schoolName = rs_school.sch_name />
							
							<!---检查学生信息合法性--->
							<cfset sql = "	SELECT
												stu.stu_id,stu.stu_name,clz.cls_id,clz.cls_name,sbj.sbj_name,
												i.institute_name,clz.grade,ss.student_prop,ss.status_code
											FROM
												t_student stu INNER JOIN t_class clz ON stu.cls_id = clz.cls_id 
												INNER JOIN t_subject sbj ON sbj.sbj_id = clz.sbj_id
												INNER JOIN t_institute i ON i.institute_id = sbj.institute_id
												INNER JOIN t_student_status ss ON ss.stu_id = stu.stu_id
											WHERE
												stu.stu_id = :studentId " />
							
							<cfset queryObj = new Query( datasource=application.dnsMaster )/>
							
							<cfset queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" )/>
							
							<cfset rs_student = queryObj.execute( sql=sql ).getResult() />
							
							<cfif rs_student.recordCount and rs_student.cls_name eq targetClassName>
								<p class="success">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>已经转入<cfoutput>#targetClassName#</cfoutput>.</p>
								<cfset studentSkip++ />
							<cfelse>
								<cfif rs_student.recordCount>
									<cfif rs_student.stu_name eq studentName> 
										<!---检查目标班级的合法性--->
										<cfset sql = "	SELECT
															i.institute_id,sbj.sbj_id,clz.cls_id,sbj.sbj_name,sbj.sbj_short,clz.cls_name,clz.grade,
															sbj.level_code,i.institute_name,i.institute_short
														FROM
															t_class clz INNER JOIN t_subject sbj ON clz.sbj_id = sbj.sbj_id INNER JOIN t_institute i ON i.institute_id = sbj.institute_id
														WHERE
															clz.cls_name = :className " />
		
										<cfset queryObj = new Query( datasource=application.dnsMaster )/>
										
										<cfset queryObj.addParam( name="className", value=targetClassName, cfsqltype="cf_sql_varchar" )/>
										
										<cfset rs_target = queryObj.execute( sql=sql ).getResult() />
										
										<cfif rs_target.recordCount and (rs_target.sbj_name neq targetSubjectName AND rs_target.sbj_short neq targetSubjectName)>
											<p class="error">班级:<cfoutput>#targetClassName#</cfoutput>不属于专业:<cfoutput>#targetSubjectName#</cfoutput>不匹配.</p>
											<cfset studentError++ />
										<cfelseif rs_target.recordCount and (rs_target.institute_name neq targetInstituteName AND rs_target.institute_short neq targetInstituteName)>
											<p class="error">班级:<cfoutput>#targetClassName#</cfoutput>不属于学院:<cfoutput>#targetInstituteName#</cfoutput>不匹配.</p>
											<cfset studentError++ />
										<cfelse>
											<cfif rs_target.recordCount>
												<!---开始转专业逻辑--->
			
												<!---准备必要数据--->
												<cfset description = "学生：" & rs_student.stu_name & "(" & rs_student.stu_id & ") 从：" & rs_student.sbj_name & " 专业 转出到: " & rs_target.sbj_name & " 专业 " & rs_target.cls_name  />
												
												<cfset changeId = sequenceStudentChange.getID() />
												
												<cfset sql = "	INSERT INTO t_stuchange  
																(
																	 stuchg_id,
																	 change_date,
																	 approve_date,
																	 approve_docid,
																	 stu_source,
																	 stu_newlocation,
																	 op_description,
																	 institute_name,
																	 subject_name,
																	 grade,
																	 class_name,
																	 term_id,
																	 stu_id,
																	 admin_user_id,
																	 sct_code 
																) VALUES (
																	:changeID,
																	:changeDate,
																	:approveDate,
																	:approveDocument,
																	:source,
																	:target,
																	:description,
																	:institute,
																	:subject,
																	:grade,
																	:formClass,
																	:term,
																	:student,
																	:admin,
																	:type 	
																)" />
												
												<cfset queryObj = new Query( datasource=application.dnsMaster )/>
												
												<cfset queryObj.addParam( name="changeID", value=changeId, cfsqltype = "cf_sql_char")/>
												<cfset queryObj.addParam( name="changeDate", value=replace(dateFormat(now(), 'YYYY-MM-DD'),"-","","ALL"), cfsqltype = "cf_sql_char")/>
												<cfset queryObj.addParam( name="approveDate", value=replace(dateFormat(now(), 'YYYY-MM-DD'),"-","","ALL"), cfsqltype = "cf_sql_char")/>
												<cfset queryObj.addParam( name="approveDocument", value=approveDocument, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="source", value=schoolName, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="target", value=schoolName, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="description", value=description, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="institute", value=rs_student.institute_name, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="subject", value=rs_student.sbj_name, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="grade", value=rs_student.grade, cfsqltype = "cf_sql_char")/>
												<cfset queryObj.addParam( name="formClass", value=rs_student.cls_name, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="term", value=termId, cfsqltype = "cf_sql_char")/>
												<cfset queryObj.addParam( name="student", value=studentId, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="admin", value=tchId, cfsqltype = "cf_sql_varchar")/>
												<cfset queryObj.addParam( name="type", value=changeType, cfsqltype = "cf_sql_char")/>
											
												<cfset queryObj.execute( sql=sql ) />
											
												<cfset propAdvice.parseProperty( rs_student.student_prop ) />
												
												<cfif changeType eq "03" or changeType eq "12" or changeType eq "23">
													<cfset propAdvice.setInSchool( "1" )/>
												</cfif>
												
												<cfset statusCode = rs_student.status_code />
						
												<cfif changeType eq "03" or changeType eq "23">
													<cfset statusCode = "01"/>
												</cfif>
			
												<cfif changeType eq "12">
													<cfset statusCode = "05"/>
												</cfif>
												
												<!---调整学生状态--->
												<cfset studentProps = propAdvice.stringProperty() />
												
												<!---更新班级--->
												<cfset sql = "	UPDATE 
																	t_student a 
																SET 
																	a.cls_id = :formClass 
																WHERE 
																	a.stu_id = :studentId "/>
								
												<cfset queryObj = new Query( datasource=application.dnsMaster )/>
			
												<cfset queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" )/>
												<cfset queryObj.addParam( name="formClass", value=rs_target.cls_id, cfsqltype="cf_sql_char" )/>
												
												<cfset queryObj.execute(sql=sql)/>
												
												<!---更新其他学籍信息--->
												<cfset sql = "	UPDATE 
																	t_student_status b
																SET    
																	b.student_prop = :studentProps,
																	b.grade = :grade,
																	b.level_code = :studentLevel,
																	b.status_code = :statusCode 
																WHERE 
																	b.stu_id = :studentId "/>
			
												<cfset queryObj = new Query( datasource=application.dnsMaster )/>
												
												<cfset queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" )/>
												<cfset queryObj.addParam( name="studentProps", value=studentProps, cfsqltype="cf_sql_varchar" )/>
												<cfset queryObj.addParam( name="grade", value=rs_target.grade, cfsqltype="cf_sql_char" )/>
												<cfset queryObj.addParam( name="studentLevel", value=rs_target.level_code, cfsqltype="cf_sql_char" )/>
												<cfset queryObj.addParam( name="statusCode", value=statusCode, cfsqltype="cf_sql_char" )/>
												
												<cfset queryObj.execute( sql=sql )/>
												
												<!---更新班级人数--->
												
												<!---目标班级新增一人--->
												<cfset sql = "	UPDATE
																	t_class clz
																SET
																	clz.cls_scale = clz.cls_scale + 1 
																WHERE
																	clz.cls_id = :classId " />
											
												<cfset queryObj = new Query(datasource=application.dnsMaster)/>
												
												<cfset queryObj.addParam(name="classId", value=rs_target.cls_id, cfsqltype="cf_sql_char")/>
												
												<cfset queryObj.execute(sql=sql)/>	
												
												<!---老班级减少一人--->
												<cfset sql = "	UPDATE
																	t_class clz
																SET
																	clz.cls_scale = clz.cls_scale - 1 
																WHERE
																	clz.cls_id = :classId " />
											
												<cfset queryObj = new Query(datasource=application.dnsMaster)/>
												
												<cfset queryObj.addParam(name="classId", value=rs_student.cls_id, cfsqltype="cf_sql_char")/>
												
												<cfset queryObj.execute(sql=sql)/>
												
												<cfset studentChange++ />
		
											<cfelse>
												<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>)<cfif not len(targetClassName)>没有班级信息，请完善班级信息再次导入.<cfelse>转入班级:<cfoutput>#targetClassName#</cfoutput>，录入不正确. 找不到对应的班级数据记录.</cfif></p>
												<cfset studentError++ />
											</cfif>
										</cfif>
									<cfelse>
										<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 学号与姓名不匹配.</p>
										<cfset studentError++ />
									</cfif>
								<cfelse>
									<p class="error">学生:<cfoutput>#rs["姓名"][rs.currentRow]#</cfoutput>(<cfoutput>#rs["学号"][rs.currentRow]#</cfoutput>) 录入不正确. 没有对应学号的学生.</p>
									<cfset studentError++ />
								</cfif>
							</cfif>
						</cfloop>
					</cftransaction>
				</cfif>
			</cfif>
		</cfif> 
		
		<p>更新数据 [<cfoutput>#studentChange#</cfoutput>] 条.</p>
		<p>错误数据 [<cfoutput>#studentError#</cfoutput>] 条.</p>
		<p>跳过存在数据 [<cfoutput>#studentSkip#</cfoutput>] 条.</p>

		<!--- 删除临时文件 --->
		<cfif fileExists(temFile)>
			<cffile action="delete" file="#temFile#" />		
		</cfif>
	
	</cfif>

</body>
</html>